package com.theydream.qeechain.model.map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author SQLite, To help on memory access.
 *
 */
public class SpotDbAdapter {

	// ================================================================
	// Constants
	// ================================================================

	public static final String KEY_SPOT = "spot";
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";
	public static final String KEY_ROWID = "_id";
	public static final String KEY_PIC="pic";
	private static final String TAG = "SpotDbAdapter";

	/**
	 * Database creation sql statement Each row contains ID, spot Name, Latitude
	 * and Longitude
	 */
	private static final String DATABASE_CREATE = "create table spotgeocode (_id integer primary key autoincrement, "
			+ "spot text not null, latitude double, longitude double, pic text not null);";
	private static final String DATABASE_NAME = "Spotdata";
	private static final String DATABASE_TABLE = "spotgeocode";
	private static final int DATABASE_VERSION = 1;

	// ================================================================
	// Fields
	// ================================================================

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;

	// ================================================================
	// Constructors
	// ================================================================

	public SpotDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	// ================================================================
	// Methods
	// ================================================================

	public SpotDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	// Create a new row in the database corresponding to a new location
	public long createSpotinfo(String spot, double latitude, double longitude, String pic) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SPOT, spot);
		initialValues.put(KEY_LATITUDE, latitude);
		initialValues.put(KEY_LONGITUDE, longitude);
		initialValues.put(KEY_PIC, pic);
		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}

	/**
	 * Delete the note with the given rowId
	 * 
	 * @param rowId
	 *            id of spot info to delete
	 * @return true if deleted, false otherwise
	 */
	public boolean deleteSpotinfo(long rowId) {
		return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * Return a Cursor over the list of all spot info in the database
	 * 
	 * @return Cursor over all spots
	 */
	public Cursor fetchAllSpotinfo() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_SPOT, KEY_LATITUDE, KEY_LONGITUDE, KEY_PIC }, null, null, null, null,null);
	}

	/**
	 * Return a Cursor positioned at the note that matches the given rowId
	 * 
	 * @param rowId
	 *            id of spot info to retrieve
	 * @return Cursor positioned to matching spot info, if found
	 * @throws SQLException
	 *             if note could not be found/retrieved
	 */
	public Cursor fetchSpotinfo(long rowId) throws SQLException {
		Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {
				KEY_ROWID, KEY_SPOT, KEY_LATITUDE, KEY_LONGITUDE, KEY_PIC }, KEY_ROWID
				+ "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/**
	 * Update the spot info using the details provided. The spot info to be
	 * updated is specified using the rowId, and it is altered to use the spot
	 * name, latitude, and longitude values passed in
	 * 
	 * @param rowId
	 *            id of spot info to update
	 * @param spot
	 *            info value to set spot name to
	 * @param latitude
	 *            value to set latitude to
	 * @return true if the note was successfully updated, false otherwise
	 */
	public boolean updateSpotinfo(long rowId, String spot, double latitude,
			double longitude, String pic) {
		ContentValues args = new ContentValues();
		args.put(KEY_SPOT, spot);
		args.put(KEY_LATITUDE, latitude);
		args.put(KEY_LONGITUDE, longitude);
		args.put(KEY_PIC, pic);
		return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
	}

	// ================================================================
	// Inner Classes
	// ================================================================

	// This is the database SQLite Open helper. This provides the methods to
	// create, open and update the database
	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		// Create the SQLite database if it does not exist
		@Override
		public void onCreate(SQLiteDatabase db) {
			// Execute the SQL statement to create the database and the table
			db.execSQL(DATABASE_CREATE);

			// HttpTextDiyService diyService= new HttpTextDiyService();
			// while (true)
			// {
			// }
			insertSpotInfo(db, "Acid", 22.334004, 114.265971,"acidbear");
			insertSpotInfo(db, "Adidas", 22.337513, 114.261505,"adidasbear");
			insertSpotInfo(db, "Atomic", 22.338039, 114.263971,"atomicbearpink");
			insertSpotInfo(db, "Blue Dog", 22.337225, 114.263756,"bestioleclearbluedog");
			insertSpotInfo(db, "Brit Bear", 22.337324, 114.263359,"britbear");
			insertSpotInfo(db, "Brooklyn Bomber", 22.322131, 114.263037,"brooklynbomber");
//			insertSpotInfo(db, "Red Diamond", 22.336521, 114.263713,"red_diamond_256");
			insertSpotInfo(db, "Cage Match Cat", 22.336590, 114.263370,"cagematchcat");
			insertSpotInfo(db, "Chesire Cat", 22.336511, 114.264089,"chesirecat");
			insertSpotInfo(db, "Green Diamond", 22.337037, 114.267100,"green_diamond_96");
			insertSpotInfo(db, "Clear Dog", 22.337056, 114.268466,"cleardog");
			insertSpotInfo(db, "Clear Toyer", 22.337969, 114.265280,"cleartoyer");
			insertSpotInfo(db, "Darkside", 22.337017, 114.264432,"darkside");
			insertSpotInfo(db, "Devil 666", 22.338912, 114.264829,"devil666bear");
			insertSpotInfo(db, "Dokuroinu Dog", 22.335746, 114.263445,"dokuroinudog");
			
			insertSpotInfo(db, "White Bone", 22.315540, 114.218851,"whitebonetoyer");
			insertSpotInfo(db, "Puzzle Blue", 22.314359, 114.217569,"puzzlebearblue");
			insertSpotInfo(db, "Blaze", 22.313654, 114.218781,"nokiablaze");
			insertSpotInfo(db, "PouPou", 22.314170, 114.219897,"poupou");
			insertSpotInfo(db, "Killer Cat", 22.313624,114.221828,"killercat");
			insertSpotInfo(db, "Fang Wolf", 22.312652,114.219768,"fangwolf");
			
			
//			insertSpotInfo(db, "Water", 22.338406, 114.263209,"water");
			
			
//			insertSpotInfo(db, "Wo Cow ", 22.334004, 114.265971,"wo_cow");
//			insertSpotInfo(db, "Fire Horse", 22.337513, 114.261505,"fire_horse");
//			insertSpotInfo(db, "Fire Bird", 22.338039, 114.263971,"fire_bird");
//			insertSpotInfo(db, "Ice Bird", 22.337225, 114.263756,"ice_bird");
//			insertSpotInfo(db, "Cuty", 22.337324, 114.263359,"cuty");
//			insertSpotInfo(db, "Darkness", 22.322131, 114.263037,"darkness");
//			insertSpotInfo(db, "Red Diamond", 22.336521, 114.263713,"red_diamond_256");
//			insertSpotInfo(db, "Fire Cow", 22.336590, 114.263370,"fire_cow");
//			insertSpotInfo(db, "Green Dragon", 22.336511, 114.264089,"green_dragon");
//			insertSpotInfo(db, "Green Diamond", 22.337037, 114.267100,"green_diamond_96");
//			insertSpotInfo(db, "Knife", 22.337056, 114.268466,"knife");
//			insertSpotInfo(db, "Mouse", 22.337969, 114.265280,"mouse");
//			insertSpotInfo(db, "Water Mouse", 22.337017, 114.264432,"water_mouse");
//			insertSpotInfo(db, "Red Cat", 22.338912, 114.264829,"red_cat");
//			insertSpotInfo(db, "Thunder Bird", 22.335746, 114.263445,"thunder_bird");
//			insertSpotInfo(db, "Water", 22.338406, 114.263209,"water");
//			insertSpotInfo(db, "Little Red", 22.335786, 114.260930,"little_red");
//			insertSpotInfo(db, "White Diamond", 22.337594, 114.263552,"white_diamond_256");
//			insertSpotInfo(db, "Purple Diamond", 22.336332, 114.262952,"purple_diamond_128");
//			insertSpotInfo(db, "Whale", 22.340034, 114.269142,"whale");
//			insertSpotInfo(db, "Walrein", 22.334357, 114.273133,"walrein");
//			insertSpotInfo(db, "Ultimate", 22.336759, 114.262984,"ultimate");
			
//			insertSpotInfo(db, "mission_1", 22.428325,114.242771,"mission");
//			insertSpotInfo(db, "mission_2", 22.337594, 114.263552,"mission");
//			insertSpotInfo(db, "mission_3", 22.336332, 114.262952,"mission");
//			insertSpotInfo(db, "mission_4", 22.340034, 114.269142,"mission");
//			insertSpotInfo(db, "mission_5", 22.334357, 114.273133,"mission");
//			insertSpotInfo(db, "mission_6", 22.336759, 114.262984,"mission");
			

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS spotgeocode");
			onCreate(db);
		}

		// Inserts a row into the database
		private void insertSpotInfo(SQLiteDatabase db, String spot,
				double latitude, double longitude, String pic) {
			ContentValues initialValues = new ContentValues();
			initialValues.put(KEY_SPOT, spot);
			initialValues.put(KEY_LATITUDE, latitude);
			initialValues.put(KEY_LONGITUDE, longitude);
			initialValues.put(KEY_PIC, pic);
			db.insert(DATABASE_TABLE, null, initialValues);
		}

	}

}
